-- limpieza de la tabla resumen antes de actualizar
TRUNCATE TABLE item_behaivor;

-- se prepara los campos para insertar los datos
INSERT INTO item_behaivor (item_id, profit, profit_star, roi, roi_star, speed, speed_star, friction, friction_star, perishable, perishable_star, inversion, last_update)

WITH BaseAverages AS (
    SELECT 
        item_id,
        AVG(profit) AS avg_profit,
        AVG(roi) AS avg_roi,
        AVG(speed) AS avg_speed,
        AVG(friction) AS avg_bulk,
        AVG(perishable) AS avg_perishable,
        AVG(inversion) AS avg_inversion,
        MAX(last_update) AS max_date
    FROM item_behaivor_history_month
    GROUP BY item_id
),

Percentiles AS (
    SELECT DISTINCT
        -- Percentiles para Profit usando datos mensuales
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_80,

        -- Percentiles para ROI usando datos mensuales
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_80,

        -- Percentiles para Speed usando datos mensuales
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY speed) OVER () AS p_speed_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY speed) OVER () AS p_speed_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY speed) OVER () AS p_speed_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY speed) OVER () AS p_speed_80,

        -- Percentiles para Bulk / Friction usando datos mensuales
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY friction) OVER () AS p_bulk_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY friction) OVER () AS p_bulk_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY friction) OVER () AS p_bulk_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY friction) OVER () AS p_bulk_80,

        -- Percentiles para Perishable usando datos mensuales
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY perishable) OVER () AS p_perishable_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY perishable) OVER () AS p_perishable_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY perishable) OVER () AS p_perishable_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY perishable) OVER () AS p_perishable_80
    FROM item_behaivor_history_month
)

SELECT 
    ba.item_id,

    ba.avg_profit,
    CASE 
        WHEN ba.avg_profit <= p.p_profit_20 THEN 1
        WHEN ba.avg_profit <= p.p_profit_40 THEN 2
        WHEN ba.avg_profit <= p.p_profit_60 THEN 3
        WHEN ba.avg_profit <= p.p_profit_80 THEN 4
        ELSE 5 
    END AS profit_star,

    ba.avg_roi,
    CASE 
        WHEN ba.avg_roi <= p.p_roi_20 THEN 1
        WHEN ba.avg_roi <= p.p_roi_40 THEN 2
        WHEN ba.avg_roi <= p.p_roi_60 THEN 3
        WHEN ba.avg_roi <= p.p_roi_80 THEN 4
        ELSE 5 
    END AS roi_star,

    ba.avg_speed,
    CASE 
        WHEN ba.avg_speed <= p.p_speed_20 THEN 5
        WHEN ba.avg_speed <= p.p_speed_40 THEN 4
        WHEN ba.avg_speed <= p.p_speed_60 THEN 3
        WHEN ba.avg_speed <= p.p_speed_80 THEN 2
        ELSE 1
    END AS speed_star,

    ba.avg_bulk,
    CASE 
        WHEN ba.avg_bulk <= p.p_bulk_20 THEN 5
        WHEN ba.avg_bulk <= p.p_bulk_40 THEN 4
        WHEN ba.avg_bulk <= p.p_bulk_60 THEN 3
        WHEN ba.avg_bulk <= p.p_bulk_80 THEN 2
        ELSE 1
    END AS bulk_star,

    ba.avg_perishable,
    CASE 
        WHEN ba.avg_perishable <= p.p_perishable_20 THEN 5
        WHEN ba.avg_perishable <= p.p_perishable_40 THEN 4
        WHEN ba.avg_perishable <= p.p_perishable_60 THEN 3
        WHEN ba.avg_perishable <= p.p_perishable_80 THEN 2
        ELSE 1
    END AS perishable_star,

    ba.avg_inversion,
    ba.max_date
FROM BaseAverages ba
CROSS JOIN Percentiles p;